add CTE examples to site

site/docs/examples/INSERT/0040-complex-values.js

@@ -7,7 +7,7 @@ const result = await db
   .insertInto('person')
   .values(({ ref, selectFrom, fn }) => ({
     first_name: 'Jennifer',
-    last_name: sql\`concat(\${ani}, \${ston})\`,
+    last_name: sql<string>\`>concat(\${ani}, \${ston})\`,
     middle_name: ref('first_name'),
     age: selectFrom('person')
       .select(fn.avg<number>('age').as('avg_age')),

site/docs/examples/cte/0010-simple-selects.js

@@ -0,0 +1,21 @@
+export const simpleSelects = `const result = await db
+  // Create a CTE called \`jennifers\` that selects all
+  // persons named 'Jennifer'.
+  .with('jennifers', (db) => db
+    .selectFrom('person')
+    .where('first_name', '=', 'Jennifer')
+    .select(['id', 'age'])
+  )
+  // Select all rows from the \`jennifers\` CTE and
+  // further filter it.
+  .with('adult_jennifers', (db) => db
+    .selectFrom('jennifers')
+    .where('age', '>', 18)
+    .select(['id', 'age'])
+  )
+  // Finally select all adult jennifers that are
+  // also younger than 60.
+  .selectFrom('adult_jennifers')
+  .where('age', '<', 60)
+  .selectAll()
+  .execute()`

site/docs/examples/cte/0010-simple-selects.mdx

@@ -0,0 +1,27 @@
+---
+title: 'Simple selects'
+---
+
+# Simple selects
+
+Common table expressions (CTE) are a great way to modularize complex queries.
+Essentially they allow you to run multiple separate queries within a
+single roundtrip to the DB.
+
+Since CTEs are a part of the main query, query optimizers inside DB
+engines are able to optimize the overall query. For example, postgres
+is able to inline the CTEs inside the using queries if it decides it's
+faster.
+
+import {
+  Playground,
+  exampleSetup,
+} from '../../../src/components/Playground'
+
+import {
+  simpleSelects
+} from './0010-simple-selects'
+
+<div style={{ marginBottom: '1em' }}>
+  <Playground code={simpleSelects} setupCode={exampleSetup} />
+</div>

site/docs/examples/cte/0020-inserts-updates-and-deletions.js

@@ -0,0 +1,28 @@
+export const insertsUpdatesAndDeletions = `const result = await db
+  .with('new_person', (db) => db
+    .insertInto('person')
+    .values({
+      first_name: 'Jennifer',
+      age: 35,
+    })
+    .returning('id')
+  )
+  .with('new_pet', (db) => db
+    .insertInto('pet')
+    .values({
+      name: 'Doggo',
+      species: 'dog',
+      is_favorite: true,
+      // Use the id of the person we just inserted.
+      owner_id: db
+        .selectFrom('new_person')
+        .select('id')
+    })
+    .returning('id')
+  )
+  .selectFrom(['new_person', 'new_pet'])
+  .select([
+    'new_person.id as person_id',
+    'new_pet.id as pet_id'
+  ])
+  .execute()`

site/docs/examples/cte/0020-inserts-updates-and-deletions.mdx

@@ -0,0 +1,21 @@
+---
+title: 'Inserts, updates and deletions'
+---
+
+# Inserts, updates and deletions
+
+Some databases like postgres also allow you to run other queries than selects
+in CTEs. On these databases CTEs are extremely powerful:
+
+import {
+  Playground,
+  exampleSetup,
+} from '../../../src/components/Playground'
+
+import {
+  insertsUpdatesAndDeletions
+} from './0020-inserts-updates-and-deletions'
+
+<div style={{ marginBottom: '1em' }}>
+  <Playground code={insertsUpdatesAndDeletions} setupCode={exampleSetup} />
+</div>

site/docs/examples/cte/_category_.json

@@ -0,0 +1,8 @@
+{
+  "label": "CTE",
+  "position": 7,
+  "link": {
+    "type": "generated-index",
+    "description": "Short and simple examples of how to use common table expressions (CTE) in queries."
+  }
+}

src/query-creator.ts

@@ -564,24 +564,77 @@ export class QueryCreator<DB> {
    *
    * ### Examples
    *
+   * <!-- siteExample("cte", "Simple selects", 10) -->
+   *
+   * Common table expressions (CTE) are a great way to modularize complex queries.
+   * Essentially they allow you to run multiple separate queries within a
+   * single roundtrip to the DB.
+   *
+   * Since CTEs are a part of the main query, query optimizers inside DB
+   * engines are able to optimize the overall query. For example, postgres
+   * is able to inline the CTEs inside the using queries if it decides it's
+   * faster.
+   *
    * ```ts
-   * await db
+   * const result = await db
+   *   // Create a CTE called `jennifers` that selects all
+   *   // persons named 'Jennifer'.
    *   .with('jennifers', (db) => db
    *     .selectFrom('person')
    *     .where('first_name', '=', 'Jennifer')
    *     .select(['id', 'age'])
    *   )
+   *   // Select all rows from the `jennifers` CTE and
+   *   // further filter it.
    *   .with('adult_jennifers', (db) => db
    *     .selectFrom('jennifers')
    *     .where('age', '>', 18)
    *     .select(['id', 'age'])
    *   )
+   *   // Finally select all adult jennifers that are
+   *   // also younger than 60.
    *   .selectFrom('adult_jennifers')
    *   .where('age', '<', 60)
    *   .selectAll()
    *   .execute()
    * ```
    *
+   * <!-- siteExample("cte", "Inserts, updates and deletions", 20) -->
+   *
+   * Some databases like postgres also allow you to run other queries than selects
+   * in CTEs. On these databases CTEs are extremely powerful:
+   *
+   * ```ts
+   * const result = await db
+   *   .with('new_person', (db) => db
+   *     .insertInto('person')
+   *     .values({
+   *       first_name: 'Jennifer',
+   *       age: 35,
+   *     })
+   *     .returning('id')
+   *   )
+   *   .with('new_pet', (db) => db
+   *     .insertInto('pet')
+   *     .values({
+   *       name: 'Doggo',
+   *       species: 'dog',
+   *       is_favorite: true,
+   *       // Use the id of the person we just inserted.
+   *       owner_id: db
+   *         .selectFrom('new_person')
+   *         .select('id')
+   *     })
+   *     .returning('id')
+   *   )
+   *   .selectFrom(['new_person', 'new_pet'])
+   *   .select([
+   *     'new_person.id as person_id',
+   *     'new_pet.id as pet_id'
+   *   ])
+   *   .execute()
+   * ```
+   *
    * The CTE name can optionally specify column names in addition to
    * a name. In that case Kysely requires the expression to retun
    * rows with the same columns.